System Parameters¶

This chapter provides information about configuring system parameters that can affect the system performance. System parameters determine overall performance and operation of the system. This chapter explains how to use configuration files for database server and broker as well as a description of each parameter. For CUBRID Manager server configuration, see CUBRID Manager Manual.

This chapter covers the following topics:

Configuring the database server
Configuring the broker

Configuring the Database Server¶

Scope of Database Server Configuration¶

CUBRID consists of the database server, the broker and the CUBRID Manager. Each component has its configuration file. The system parameter configuration file for the database server is cubrid.conf located in the $CUBRID/conf directory. System parameters configured in cubrid.conf affect overall performance and operation of the database system. Therefore, it is very important to understand the database server configuration.

The CUBRID database server has a client/server architecture. To be more specific, it is divided into a database server process linked to the server library and the broker process linked to the client library. The server process manages the database storage structure and provides concurrency and transaction functionalities. The client process prepares for query execution and manages object/schema.

System parameters for the database server, which can be set in the cubrid.conf file, are classified into a client parameter, a server parameter and a client/server parameter according to the range to which they are applied. A client parameter is only applied to client processes such as the broker. A server parameter affects the behaviors of the server processes. A client/server parameter must be applied to both server and client.

Location of cubrid.conf File and How It Works

A database server process refers only to the $CUBRID/conf/cubrid.conf file. Database-specific configurations are distinguished by sections in the cubrid.conf file.

A client process (i) refers to the $CUBRID/conf/cubrid.conf file and then (ii) additionally refers to the cubrid.conf file in the current directory ($PWD). The configuration of the file in the current directory ($PWD/cubrid.conf) overwrites that of the $CUBRID/conf/cubrid.conf file. That is, if the same parameter configuration exists in $PWD/cubrid.conf and in $CUBRID/conf/cubrid.conf, the configuration in $PWD/cubrid.conf has the priority.

Changing Database Server Configuration¶

Editing the Configuration File

You can add/delete parameters or change parameter values by manually editing the system parameter configuration file (cubrid.conf) in the $CUBRID/conf directory.

The following parameter syntax rules are applied when configuring parameters in the configuration file:

Parameter names are not case-sensitive.

The name and value of a parameter must be entered in the same line.

An equal sign (=) can be to configure the parameter value. Spaces are allowed before and after the equal sign.

If the value of a parameter is a character string, enter the character string without quotes. However, use quotes if spaces are included in the character string.

Using SQL Statements

You can configure a parameter value by using SQL statements in the CSQL Interpreter or CUBRID Manager's Query Editor. Note that you cannot change every parameter. For updatable parameters, see cubrid.conf Configuration File and Default Parameters.
SET SYSTEM PARAMETERS 'parameter_name=value [{; name=value}...]'
parameter_name is the name of a client parameter whose value is editable. In this syntax, value is the value of the given parameter. You can change multiple parameter values by separating them with semicolons (;). You must take caution when you apply changes of parameter values.

The following example shows how to retrieve the result of an index scan in OID order and configure the number of queries to be stored in the history of the CSQL Interpreter to 70.
SET SYSTEM PARAMETERS 'index_scan_in_oid_order=1; csql_history_num=70';

Using Session Commands of the CSQL Interpreter

You can configure system parameter values by using session commands (;SET) in the CSQL Interpreter. Note that you cannot change every parameter. For updatable parameters, see cubrid.conf Configuration File and Default Parameters.

The following example shows how to configure the block_ddl_statement parameter to 1 so that execution of DDL statements is not allowed.
csql> ;se block_ddl_statement=1
=== Set Param Input ===
block_ddl_statement=1

cubrid.conf Configuration File and Default Parameters¶

CUBRID consists of the database server, the broker and the CUBRID Manager. The name of the configuration file for each component is as follows. These files are all located in the $CUBRID/conf directory.

Database server configuration file: cubrid.conf
Broker configuration file: cubrid_broker.conf
CUBRID Manager server configuration file: cm.conf

cubrid.conf is a configuration file that sets system parameters for the CUBRID database server and determines overall performance and operation of the database system. In the cubrid.conf file, some important parameters needed for system installation are provided, having their default values.

Database Server System Parameters

The following are database server system parameters that can be used in the cubrid.conf configuration file. On the following table, "Applied" column's "client parameter" means that they are applied to CAS, CSQL, cubrid utilities. Its "server parameter" means that they are applied to the DB server (cub_server process). For the scope of client and server parameters, see Scope of Database Server Configuration.

You can change the parameters that are capable of changing dynamically the setting value through the SET SYSTEM PARAMETERS statement or a session command of the CSQL Interpreter, ;set while running the DB. If you are a DBA, you can change parameters regardless of the applied classification. However, if you are not a DBA, you can only change "session" parameters. (on the below table, a parameter of which "session" item's value is O.)

On the below table, if "Applied" is "server parameter", that parameter's applied scope is global. If "Applied" is "client parameter" or "client/server parameter", that parameter's applied scope is session.

"Dynamic Change" and "Session or not" are marked on the below table. The affected range of the parameter which "Dynamic Change" is "available" depends on "Applied" and "Session" items.

If "Dynamic Change" is "available" and "Applied" is "server parameter", that parameter's changed value is applied to DB server. Then applications use the changed value of the parameter until the DB server is restarted.

If "Dynamic Change" is "available" and "Applied" is "client parameter", this belongs to the "session" parameter and that parameter's changed value is applied only to that DB session. In other words, the changed value is only applied to the application which requested to change that value. For example, if block_ddl_statement parameter's value is changed into yes, then only the application requested to change that parameter cannot use DDL statements.

If "Dynamic Change" is "available", "Applied" is "client parameter" and;

this belongs to the "session" parameter, that parameter's changed value is applied only to that DB session. In other words, the changed value is only applied to the application requested to change that value. For example, if add_column_update_hard_default parameter's value is changed into yes, then only the application requested to change that parameter lets the newly added column with NOT NULL constraint have hard default value.

this does not belong to the "session" parameter, the values of "client" side and "server" side are changed. For example, error_log_level parameter is applied to each of "server" side and "client" side; if this value is changed from "ERROR" into "WARNING", this is applied only to "server" (cub_server process) and "client" (CAS or CSQL) which requested to change this value. Other "clients" keeps the value of "ERROR".

Note

If you want to change the value of a parameter permanently, restart all of DB server and broker after changing configuration values of cubrid.conf.

Category Parameter Name Applied Session Type Default Value Dynamic Change

Connection-Related Parameters cubrid_port_id client parameter int 1,523

check_peer_alive client/server parameter O string both available

db_hosts client parameter O string NULL available

max_clients server parameter int 100

tcp_keepalive client/server parameter bool yes

Memory-Related Parameters data_buffer_size server parameter byte 32,768 * db_page_size

index_scan_oid_buffer_size server parameter byte 4 * db_page_size

max_agg_hash_size server parameter byte 2,097,152(2M)

sort_buffer_size server parameter byte 128 * db_page_size

temp_file_memory_size_in_pages server parameter int 4

thread_stacksize server parameter byte 1,048,576

Disk-Related Parameters db_volume_size server parameter byte 512M

dont_reuse_heap_file server parameter bool no

generic_vol_prealloc_size server parameter byte 50M

log_volume_size server parameter byte 512M

temp_file_max_size_in_pages server parameter int -1

temp_volume_path server parameter string NULL

unfill_factor server parameter float 0.1

volume_extension_path server parameter string NULL

Error Message-Related Parameters call_stack_dump_activation_list client/server parameter string DEFAULT DBA only

call_stack_dump_deactivation_list client/server parameter string NULL DBA only

call_stack_dump_on_error client/server parameter bool no DBA only

error_log client/server parameter string cub_client.err, cub_server.err

error_log_level client/server parameter string SYNTAX DBA only

error_log_warning client/server parameter bool no DBA only

error_log_size client/server parameter int 8,000,000 DBA only

Concurrency/Lock-Related Parameters deadlock_detection_interval_in_secs server parameter float 1.0 DBA only

isolation_level client parameter O int 3 available

lock_escalation server parameter int 100,000

lock_timeout client parameter O msec -1 available

rollback_on_lock_escalation server parameter bool no DBA only

Logging-Related Parameters adaptive_flush_control server parameter bool yes DBA only

background_archiving server parameter bool yes DBA only

checkpoint_every_size server parameter byte 10,000 * log_page_size

checkpoint_interval server parameter msec 6min DBA only

checkpoint_sleep_msecs server parameter msec 1 DBA only

force_remove_log_archives server parameter bool yes DBA only

log_buffer_size server parameter byte 128 * log_page_size

log_max_archives server parameter int INT_MAX DBA only

log_trace_flush_time server parameter msec 0 DBA only

max_flush_size_per_second server parameter byte 10,000 * db_page_size DBA only

remove_log_archive_interval_in_secs server parameter sec 0 DBA only

sync_on_flush_size server parameter byte 200 * db_page_size DBA only

Transaction Processing-Related Parameters async_commit server parameter bool no

group_commit_interval_in_msecs server parameter msec 0 DBA only

Statement/Type-Related Parameters add_column_update_hard_default client/server parameter O bool no available

alter_table_change_type_strict client/server parameter O bool no available

ansi_quotes client parameter bool yes

block_ddl_statement client parameter O bool no available

block_nowhere_statement client parameter O bool no available

compat_numeric_division_scale client/server parameter O bool no available

default_week_format client/server parameter O int 0 available

group_concat_max_len server parameter O byte 1,024 DBA only

intl_check_input_string client parameter O bool no available

intl_collation client parameter O string available

intl_date_lang client parameter O string available

intl_number_lang client parameter O string available

no_backslash_escapes client parameter bool yes

only_full_group_by client parameter O bool no available

oracle_style_empty_string client parameter bool no

pipes_as_concat client parameter bool yes

plus_as_concat client parameter bool yes

require_like_escape_character client parameter bool no

return_null_on_function_errors client/server parameter O bool no available

string_max_size_bytes client/server parameter O byte 1,048,576 available

unicode_input_normalization client parameter O bool no available

unicode_output_normalization client parameter O bool no available

Query Plan Cache-Related Parameters max_plan_cache_entries client/server parameter O int 1,000

max_filter_pred_cache_entries client/server parameter int 1,000

Utility-Related Parameters backup_volume_max_size_bytes server parameter byte 0

communication_histogram client parameter O bool no available

compactdb_page_reclaim_only server parameter int 0

csql_history_num client parameter O int 50 available

HA-Related Parameters ha_mode server parameter string off

Other Parameters access_ip_control server parameter bool no

access_ip_control_file server parameter string

agg_hash_respect_order client parameter O bool yes available

auto_restart_server server parameter O bool yes DBA only

index_scan_in_oid_order client parameter O bool no available

index_unfill_factor server parameter float 0.05

java_stored_procedure server parameter bool no

multi_range_optimization_limit server parameter O int 100 DBA only

optimizer_enable_merge_join client parameter O bool no available

pthread_scope_process server parameter bool yes

server server parameter string

service server parameter string

session_state_timeout server parameter sec 21,600

sort_limit_max_count client parameter O int 1,000 available

sql_trace_slow server parameter O msec -1 DBA only

sql_trace_execution_plan server parameter O bool no DBA only

use_orderby_sort_limit server parameter O bool yes DBA only

log_page_size: A log volume page size specified by --log-page-size option when you are creating database. Default: 16KB. log page related parameter's value is rounded off by page unit. For example, the value of checkpoint_every_size is divided by 16KB and its decimal point is dropped, then it is multiplied by 16KB.

log_page_size: A DB volume page size specified by --db-page-size option when you are creating database. Default: 16KB. DB page related parameter's value is rounded off by page unit. For example, the value of data_buffer_size is divided by 16KB and its decimal point is dropped, then it is multiplied by 16KB.

Section by Parameter

Parameters specified in cubrid.conf have the following four sections:

Used when the CUBRID service starts: [service] section

Applied commonly to all databases: [common] section

Applied individually to each database: [@<database>] section

Used only when the cubrid utilities are run with stand-alone mode(--SA-mode): [standalone] section

Where <database> is the name of the database to which each parameter applies. If a parameter configured in [common] is the same as the one configured in [@<database>], the one configured in [@<database>] is applied.
.....
[common]
.....
sort_buffer_size=2M
.....
[standalone]

sort_buffer_size=256M
.....
Configuration defined in [standalone] is used only when cubrid utilities started with "cubrid" are run with stand-alone mode. For example, on the above configuration, if DB is started with --CS-mode(default)(cubrid databases start db_name), "sort_buffer_size=2M" is applied. However, if DB is stopped and "cubrid loaddb --SA-mode" is executed, "sort_buffer_size=256M" is applied. If you run "cubrid loaddb --SA-mode", bigger size of sort buffer will be required during index creation; therefore, increasing sort buffer size will be better for the performance of "loaddb" execution.

Default Parameters

cubrid.conf, a default database configuration file created during the CUBRID installation, includes some default database server parameters that must be changed. You can change the value of a parameter that is not included as a default parameter by manually adding or editing one.

The following is the content of the cubrid.conf file.

# Copyright (C) 2008 Search Solution Corporation. All rights reserved by Search Solution.
#
# $Id$
#
# cubrid.conf#

# For complete information on parameters, see the CUBRID
# Database Administration Guide chapter on System Parameters

# Service section - a section for 'cubrid service' command
[service]

# The list of processes to be started automatically by 'cubrid service start' command
# Any combinations are available with server, broker and manager.
service=server,broker,manager

# The list of database servers in all by 'cubrid service start' command.
# This property is effective only when the above 'service' property contains 'server' keyword.
#server=demodb,testdb

# Common section - properties for all databases
# This section will be applied before other database specific sections.
[common]

# Read the manual for detailed description of system parameters
# Manual > System Configuration > Database Server Configuration > Default Parameters

# Size of data buffer are using K, M, G, T unit
data_buffer_size=512M

# Size of log buffer are using K, M, G, T unit
log_buffer_size=4M

# Size of sort buffer are using K, M, G, T unit
# The sort buffer should be allocated per thread.
# So, the max size of the sort buffer is sort_buffer_size * max_clients.
sort_buffer_size=2M

# The maximum number of concurrent client connections the server will accept.
# This value also means the total # of concurrent transactions.
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

If you want to set data_buffer_size as 128M and max_clients as 10 only on testdb, set as follows.

[service]

service=server,broker,manager

[common]

data_buffer_size=512M
log_buffer_size=4M
sort_buffer_size=2M
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

[@testdb]
data_buffer_size=128M
max_clients=10

Connection-Related Parameters¶

The following are parameters related to the database server. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
cubrid_port_id	int	1,523	1
check_peer_alive	string	both
db_hosts	string	NULL
max_clients	int	100	10	10,000
tcp_keepalive	bool	yes

cubrid_port_id

cubrid_port_id is a parameter to configure the port to be used by the master process. The default value is 1,523. If the port 1,523 is already being used on the server where CUBRID is installed or it is blocked by a firewall, an error message, which means the master server is not connected because the master process cannot be running properly, is displayed. If such port conflict occurs, the administrator must change the value of cubrid_port_id considering the server environment.

check_peer_alive

check_peer_alive is a parameter to decide whether you execute the function checking that the client/server processes work well. The default is both.

The client processes connecting with a server process are the broker application server(cub_cas) process, the process copying replication logs(copylogdb), the process applying replication logs. (applylogdb), CSQL interpreter(csql), etc. The server process and the client process which connected with it wait each other's response. But if one of them cannot get the data for a long time(example: exceeding 5 sec), it will check or not if the other works well based on the configuration of check_peer_alive parameter. During this processes, if it is judged that the process doesn't work properly, it disconnect forcibly.

The values and the working methods are as follows.

both: As the server process accesses to the client process by ECHO(7) port, it checks if the client process works well. The client process also does the same thing to the server process(The default value).

server_only: Only the server process checks whether the client process works well.

client_only: Only the client process checks whether the server process works well.

none: None of the server and client processes check whether the other process works well.

Specially, if ECHO(7) port is blocked by the firewall configuration, each process can mistake that the other process was exited. Therefore, you should avoid this problem by setting this parameter's value as none.

db_hosts

db_hosts is a parameter to configure a list of the database server hosts to which clients can connect, and the connection order. The server host list consists of multiple server host names, and host names are separated by spaces or colons (:). Duplicate or non-existent names are ignored.

The following example shows the values of the db_hosts parameter. In this example, connections are attempted in the order of host1 > host2 > host3.
db_hosts="hosts1:hosts2:hosts3"
To connect to the server, the client first tries to connect to the specified server host referring to the database location file (databases.txt). If the connection fails, the client then tries to connect to the first one of the secondarily specified server hosts by referring to the value of the db_hosts parameter in the database configuration file (cubrid.conf).

max_clients

max_clients is a parameter to configure the maximum number of clients (usually broker application processes (CAS)) which allow concurrent connections to the database server. The max_clients parameter refers to the number of concurrent transactions per database server process. The default value is 100.

To guarantee performance while increasing the number of concurrent users in CUBRID environment, you need to make the appropriate value of the max_clients (cubrid.conf) parameter and the MAX_NUM_APPL_SERVER (cubrid_broker.conf) parameter. That is, you are required to configure the number of concurrent connections allowed by databases with the max_clients parameter. You should also configure the number of concurrent connections allowed by brokers with the MAX_NUM_APPL_SERVER parameter.

For example, in the cubrid_broker.conf file, two node of a broker where the MAX_NUM_APPL_SERVER value of [%query_editor] is 50 and the MAX_NUM_APPL_SERVER value of [%BROKER1] is 50 is trying to connect one database server, the concurrent connections (max_clients value) allowed by the database server can be configured as follows:

(the maximum number of 100 by each node of a broker) * (two node of a broker) + (10 spare for database server connections of internal CUBRID process such as database server connection of CSQL Interpreter or HA log replication process) = 210

Especially, in HA environment, the value must be greater than the sum specified in MAX_NUM_APPL_SERVER of every broker node which connects to the same database.

Note that the memory usage is affected by the value specified in max_clients. That is, if the number of value is high, the memory usage will increase regardless of whether or not the clients actually access the database.

Note

In Linux system, max_clients parameter is related to "ulimit -n" command, which specifies the maximum number of file descriptors which a process can use. File descriptor includes not only a file, but also a network socket. Therefore, the number of "ulimit -n" should be larger than the number of max_clients.

tcp_keepalive

tcp_keepalive is a parameter which specifies if you apply SO_KEEPALIVE option to TCP network protocol or not. The default is yes. If this value is no, DB server-side connection can be disconnected when transaction logs are not copied for a long time in the firewall environment between master and slave.

Memory-Related Parameters¶

The following are parameters related to the memory used by the database server or client. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
data_buffer_size	byte	32,768 * db_page_size	1,024 * db_page_size	2G(32bit), INT_MAX * db_page_size (64bit)
index_scan_oid_buffer_size	byte	4 * db_page_size	0.05 * db_page_size	16 * db_page_size
max_agg_hash_size	byte	2,097,152(2M)	32,768(32K)	134,217,728(128MB)
sort_buffer_size	byte	128 * db_page_size	1 * db_page_size	2G(32bit), INT_MAX * db_page_size (64bit)
temp_file_memory_size_in_pages	int	4	0	20
thread_stacksize	byte	1,048,576	65,536

data_buffer_size

data_buffer_size is a parameter to configure the size of data buffer to be cached in the memory by the database server. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 32,768 * db_page_size (512M when db_page_size is 16K), and the minimum value is 1,024 * db_page_size (16M when db_page_size is 16K). The maximum value in 64-bit CUBRID is INT_MAX * db_page_size. Note that the maximum value in 32-bit CUBRID is 2G.

The greater the value of the data_buffer_size parameter, the more data pages to be cached in the buffer, thus providing the advantage of decreased disk I/O cost. However, if this parameter is too large, the buffer pool can be swapped out by the operating system because the system memory is excessively occupied. It is recommended to configure the data_buffer_size parameter in a way the required memory size is less than two-thirds of the system memory size.

Required memory size = data buffer size (data_buffer_size)

index_scan_oid_buffer_size

index_scan_oid_buffer_size is a parameter to configure the size of buffer where the OID list is to be temporarily stored during the index scan. You can set unit K, which stands for KB (kilobytes). If you omit the unit, bytes will be applied. The default value is 4 * db_page_size (64K when db_page_size is 16K), and the minimum value is 0.05 * db_page_size (about 1K when db_page_size is 16K).

The size of the OID buffer tends to vary in proportion to the value of the index_scan_oid_buffer_size parameter and the page size set when the database was created. In addition, the bigger the size of such OID buffer, the more the index scan cost. You can set the value of the index_scan_oid_buffer_size by considering these factors.

max_agg_hash_size

max_agg_hash_size is a parameter to configure the maximum memory per transaction allocated for hashing the tuple groups in a query containing aggregation. The default is 2,097,152(2M), the minimum size is 32,768(32K), and the maximum size is 134,217,728(128MB).

If NO_HASH_AGGREGATE hint is specified, hash aggregate evaluation will not be used. As a reference, see agg_hash_respect_order.

sort_buffer_size

sort_buffer_size is a parameter to configure the size of buffer to be used when a query is processing sorting. The server assigns one sort buffer for each client's sorting-request, and releases the assigned buffer memory when sorting is complete. A sorting query includes not only SELECT sorting query, but also index-creating query.

You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 128 * db_page_size (2M when db_page_size is 16K), and the minimum value is 1 * db_page_size (16K when db_page_size is 16K).

temp_file_memory_size_in_pages

temp_file_memory_size_in_pages is a parameter to configure the number of buffer pages to cache temporary result of a query. The default value is 4 and the maximum value is 20.

Required memory size = the number of temporary memory buffer pages (temp_file_memory_size_in_pages * page size)

The number of temporary memory buffer pages = the value of the temp_file_memory_size_in_pages parameter

Page size = the value of the page size specified by the -s option of the cubrid createdb utility during the database creation

temp_file_memory_size_in_pages is a parameter to configure the number of buffer pages to cache temporary result of a query. The default value is 4 and the maximum value is 20.

Required memory size = the number of temporary memory buffer pages (temp_file_memory_size_in_pages * page size)

The number of temporary memory buffer pages = the value of the temp_file_memory_size_in_pages parameter

Page size = the value of the page size specified by the -s option of the cubrid createdb utility during the database creation

The spaces to store the temporary result are as follows.

Cache buffer to store the temporary result (acquired by temp_file_memory_size_in_pages parameter)

Permanent temp volume

Temporary temp volume

If the previous space is exhausted, then the next space is used as the following order: Cache buffer for storing temporary result -> Permanent temp volume -> Temporary temp volume.

thread_stacksize

thread_stacksize is a parameter to configure the stack size of a thread. The default value is 1048576 bytes. The value of the thread_stacksize parameter must not exceed the stack size allowed by the operating system.

Disk-Related Parameters¶

The following are disk-related parameters for defining database volumes and storing files. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
db_volume_size	byte	512M	20M	20G
dont_reuse_heap_file	bool	no
generic_vol_prealloc_size	byte	50M	0	20G
log_volume_size	byte	512M	20M	4G
temp_file_max_size_in_pages	int	-1
temp_volume_path	string	NULL
unfill_factor	float	0.1	0.0	0.3
volume_extension_path	string	NULL

db_volume_size

db_volume_size is a parameter to configure the following values. You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 512M.

The default database volume size when cubrid createdb and cubrid addvoldb utility is used without --db-volume-size option.

The default size of generic volume that is added automatically when database volume is full.

dont_reuse_heap_file

dont_reuse_heap_file is a parameter to configure whether or not heap files, which are deleted when deleting the table (DROP TABLE), are to be reused when creating a new table (CREATE TABLE). If this parameter is set to no, the deleted heap files can be reused; if it is set to yes, the deleted heap files are not used when creating a new table. The default value is no.

generic_vol_prealloc_size

Specifies the size of free space which the generic volume should always keep. If the free space size is lower than a specified size, the free space will be additionally expanded.

Checking the free space is done only when there is a new page request for the generic, data or index volume.

You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 50M. The minimum value is 0 and the maximum value is 20G.

log_volume_size

log_volume_size is a parameter to configure the default size of log volume file when the cubrid createdb utility is used without --log-volume-size option. You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB) and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 512M.

temp_file_max_size_in_pages

temp_file_max_size_in_pages is a parameter to configure the maximum number of pages to store temporary temp volumes in the disk, which are used for sorting during a query execution or creating an index; the default value is -1. Because the storage size of an intermediate result and the storage size of a final result are measured separately, if one of them are bigger than the specified size in this parameter, this query's execution is canceled as this shows an error.

If this parameter is configured to the default value, unlimited number of temporary temp volumes are created and stored in the directory specified by the temp_volume_path parameter. If it is configured to 0, the administrator must create permanent temp volumes manually by using the cubrid addvoldb utility because temporary temp volumes are not created automatically.

The temp volume which is required for a query execution is separated as a temporary temp volume and a permanent temp volume. This parameter is applied only to the temporary temp volume. To prevent the system from insufficient disk space (as the size of temporary temp volume is increased than expected because a query which requires a big-sized temp space is executed), we recommend that you should secure the expected permanent temp volume in advance and limit the size of the space used in the temporary temp volume when a query is executed.

temp_volume_path

temp_volume_path is a parameter to configure the directory in which to create temporary temp volumes used for the execution of complex queries or sorting. The default value is the volume location configured during the database creation.

unfill_factor

unfill_factor is a parameter to configure the rate of disk space to be allocated in a heap page for data updates. The default value is 0.1. That is, the rate of free space is configured to 10%. In principle, data in the table is inserted in physical order. However, if the size of the data increases due to updates and there is not enough space for storage in the given page, performance may degrade because updated data must be relocated to another page. To prevent such a problem, you can configure the rate of space for a heap page by using the unfill_factor parameter. The allowable maximum value is 0.3 (30%). In a database where data updates rarely occur, you can configure this parameter to 0.0 so that space will not be allocated in a heap page for data updates. If the value of the unfill_factor parameter is negative or greater than the maximum value, the default value (0.1) is used.

volume_extension_path

volume_extension_path is a parameter to configure the directory where automatically extended volumes are to be created. The default value is the volume location configured during the database creation.

Error Message-Related Parameters¶

The following are parameters related to processing error messages recorded by CUBRID. The type and value range for each parameter are as follows:

Parameter Name	Type	Default
call_stack_dump_activation_list	string	DEFAULT
call_stack_dump_deactivation_list	string	NULL
call_stack_dump_on_error	bool	no
error_log	string	cub_client.err, cub_server.err
error_log_level	string	SYNTAX
error_log_warning	bool	no
error_log_size	int	8,000,000

call_stack_dump_activation_list

call_stack_dump_activation_list is a parameter to configure a certain error number for which a call stack is to be dumped to a server error log (located in $CUBRID/log/server directory) as an exception even when you configure that a call stack will not be dumped for any errors. Therefore, the call_stack_dump_activation_list parameter is effective only when call_stack_dump_on_error=no.

If this value is not configured, the default value is "DEFAULT" keyword. This keyword includes below errors. "DEFAULT" keyword can be used together with other error numbers.

Error Number Error Message

-2 Internal system failure: no more specific information is available.

-7 Trying to format disk volume xxx with an incorrect value xxx for number of pages.

-13 An I/O error occurred while reading page xxx of volume xxx.

-14 An I/O error occurred while writing page xxx of volume xxx.

-17 Internal error: fetching deallocated pageid xxx of volume xxx.

-19 Internal error: pageptr = xxx of page xxx of volume xxx is not fixed.

-21 Internal error: unknown sector xxx of volume xxx.

-22 Internal error: unknown page xxx of volume xxx.

-45 Slot xxx on page xxx of volume xxx is allocated to an anchored record. A new record cannot be inserted here.

-46 Internal error: slot xxx on page xxx of volume xxx is not allocated.

-48 Accessing deleted object xxx|xxx|xxx.

-50 Internal error: relocation record of object xxx|xxx|xxx may be corrupted.

-51 Internal error: object xxx|xxx|xxx may be corrupted.

-52 Internal error: object overflow address xxx|xxx|xxx may be corrupted.

-76 Your transaction (index xxx, xxx@xxx|xxx) timed out waiting on xxx on page xxx|xxx. You are waiting for user(s) xxx to release the page lock.

-78 Internal error: an I/O error occurred while reading logical log page xxx (physical page xxx) of xxx.

-79 Internal error: an I/O error occurred while writing logical log page xxx (physical page xxx) of xxx.

-81 Internal error: logical log page xxx may be corrupted.

-90 Redo logging is always a page level logging operation. A data page pointer must be given as part of the address.

-96 Media recovery may be needed on volume xxx.

-97 Internal error: unable to find log page xxx in log archives.

-313 Object buffer underflow while reading.

-314 Object buffer overflow while writing.

-407 Unknown key xxx referenced in B+tree index {vfid: (xxx, xxx), rt_pgid: xxx, key_type: xxx}.

-414 Unknown class identifier: xxx|xxx|xxx.

-415 Invalid class identifier: xxx|xxx|xxx.

-416 Unknown representation identifier: xxx.

-417 Invalid representation identifier: xxx.

-583 Trying to allocate an invalid number (xxx) of pages.

-603 Internal Error: Sector/page table of file VFID xxx|xxx seems corrupted.

-836 LATCH ON PAGE(xxx|xxx) TIMEDOUT

-859 LATCH ON PAGE(xxx|xxx) ABORTED

-890 Partition failed.

-891 Appropriate partition does not exist.

-976 Internal error: Table size overflow (allocated size: xxx, accessed size: xxx) at file table page xxx|xxx(volume xxx)

-1040 HA generic: xxx.

-1075 Descending index scan aborted because of lower priority on B+tree with index identifier: (vfid = (xxx, xxx), rt_pgid: xxx).

The following example shows how to make error numbers only -115 and -116, perform call-stack dump.
call_stack_dump_on_error= no
call_stack_dump_activation_list=-115,-116
The following example shows how to make error numbers -115, -116 and "DEFAULT" error numbers, perform call-stack dump.
call_stack_dump_on_error= no
call_stack_dump_activation_list=-115,-116, DEFAULT

call_stack_dump_deactivation_list

call_stack_dump_deactivation_list is a parameter to configure a certain error number for which a call stack is not to be dumped when you configure that a call stack will be dumped for any errors. Therefore, the call_stack_dump_deactivation_list parameter is effective only when call_stack_dump_on_error is set to yes.

The following example shows how to configure the parameter so that call stacks will be dumped for any errors, except the ones whose numbers are -115 and -116.
call_stack_dump_on_error= yes
call_stack_dump_deactivation_list=-115,-116

call_stack_dump_on_error

call_stack_dump_on_error is a parameter to configure whether or not to dump a call stack when an error occurs in the database server. If this parameter is set to no, a call stack for any errors is not dumped. If it is set to yes, a call stack for all errors is dumped. The default value is no.

error_log

error_log is a server/client parameter to configure the name of the error log file when an error occurs in the database server. The name of the error log file must be in the form of <database_name>_<date>_<time>.err. However, the naming rule of the error log file does not apply to errors for which the system cannot find the database server information. Therefore, error logs are recorded in the cubrid.err file. The error log file cubrid.err is stored in the $CUBRID/log/server directory.

error_log_level

error_log_level is a server parameter to configure an error message to be stored based on severity. There are five different levels which ranges from NOTIFICATION (lowest level), to FATAL (highest level). The inclusion relation in messages is FATAL < ERROR < SYNTAX < WARNING < NOTIFICATION. The default is SYNTAX. If severity of error is SYNTAX, error messages with SYNTAX, ERROR, and FATAL levels are stored in the log file.

error_log_warning

error_log_warning is a parameter to configure whether or not error messages with a severity level of WARNING are to be displayed. Its default value is no. Therefore, only error messages with levels other than WARNING will be stored even though error_log_level is set to NOTIFICATION. For this reason, you must set error_log_warning to yes to store WARNING messages to an error log file.

error_log_size

error_log_size is a parameter to configure the maximum number of lines per an error log file. The default value is 8,000,000. If it reaches up the specified number, the <database_name>_<date>_<time>.err.bak file is created.

Concurrency/Lock-Related Parameters¶

The following are parameters related to concurrency control and locks of the database server. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
deadlock_detection_interval_in_secs	float	1.0	0.1
isolation_level	int	3	1	6
lock_escalation	int	100,000	5
lock_timeout	msec	-1(inf)	0(no wait)	INT_MAX
rollback_on_lock_escalation	bool	no

deadlock_detection_interval_in_secs

deadlock_detection_interval_in_secs is a parameter to configure the interval (in seconds) in which deadlocks are detected for stopped transactions. If a deadlock occurs, CUBRID resolves the problem by rolling back one of the transactions. The default value is 1 second and the minimum value is 0.1 second. This value is rounded up by 0.1 sec. unit. For example, if an input value is 0.12 seconds, the value is rounded up to 0.2 seconds. Note that deadlocks cannot be detected if the detection interval is too long.

isolation_level

isolation_level is a parameter to configure the isolation level of a transaction. The higher the isolation level, the less concurrency and the less interruption by other concurrent transactions. The isolation_level parameter can be configured to an integer value from 1 to 6, which represent isolation levels, or character strings. The default value is TRAN_REP_CLASS_UNCOMMIT_INSTANCE. For details about each isolation level and parameter values, see Transaction Isolation Level and the following table.

Isolation Level isolation_level Parameter Value

SERIALIZABLE "TRAN_SERIALIZABLE" or 6

REPEATABLE READ CLASS with REPEATABLE READ INSTANCES "TRAN_REP_CLASS_REP_INSTANCE" or "TRAN_REP_READ" or 5

REPEATABLE READ CLASS with READ COMMITTED INSTANCES(or CURSOR STABILITY) "TRAN_REP_CLASS_COMMIT_INSTANCE" or "TRAN_READ_COMMITTED" or "TRAN_CURSOR_STABILITY" or 4

REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES "TRAN_REP_CLASS_UNCOMMIT_INSTANCE" or "TRAN_READ_UNCOMMITTED" or 3

READ COMMITTED CLASS with READ COMMITTED INSTANCES "TRAN_COMMIT_CLASS_COMMIT_INSTANCE" or 2

READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES "TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE" or 1

TRAN_SERIALIZABLE : This isolation level ensures the highest level of consistency. For details, see SERIALIZABLE.

TRAN_REP_CLASS_REP_INSTANCE : This isolation level can incur phantom read. For details, see REPEATABLE READ CLASS with REPEATABLE READ INSTANCES.

TRAN_REP_CLASS_COMMIT_INSTANCE : This isolation level can incur unrepeatable read. For details, see REPEATABLE READ CLASS with READ COMMITTED INSTANCES.

TRAN_REP_CLASS_UNCOMMIT_INSTANCE : This isolation level can incur dirty read. For details, see REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES.

TRAN_COMMIT_CLASS_COMMIT_INSTANCE : This isolation level can incur unrepeatable read. It allows modification of table schema by current transactions while data is being retrieved. For details, see READ COMMITTED CLASS with READ COMMITTED INSTANCES.

TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE : This isolation level can incur dirty read. It allows modification of table schema by current transactions while data is being retrieved. For details, see READ COMMITTED CLASS with READ UNCOMMITTED INSTANCES.

lock_escalation

lock_escalation is a parameter to configure the maximum number of locks permitted before row level locking is extended to table level locking. The default value is 100,000. If the value of the lock_escalation parameter is small, the overhead by memory lock management is small as well; however, the concurrency decreases. On the other hand, if the configured value is large, the overhead is large as well; however, the concurrency increases.

lock_timeout

lock_timeout is a client parameter to configure the lock waiting time. If the lock is not permitted within the specified time period, the given transaction is canceled, and an error message is returned. If the parameter is configured to -1, which is the default value, the waiting time is infinite until the lock is permitted. If it is configured to 0, there is no waiting for locks.

You can set a unit as s, min or h, which stands for seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied, and it is rounded up to seconds. For example, 1ms will be 1s, and 1001ms will be 2s.

rollback_on_lock_escalation

It specifies rolling back the transaction or not when the lock escalation occurs. The default is no.

If this parameter is specified with yes, the error log is written without lock escalation on the lock-escalating time and this lock escalation request is failed with rolling back the transaction. If it is specified with no, the lock escalation is performed and the transaction is continued.

When the lock escalation occurs, record locks are transformed into a table lock and lock-releasing time can take long, so other transaction's access to the table can be impossible. However, if you specify the value of lock_escalation parameter(it specifies the number of record locks occurring the lock escalation) bigger, the system can overuse the memory resource.

Logging-Related Parameters¶

The following are parameters related to logs used for database backup and restore. The types and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
adaptive_flush_control	bool	yes
background_archiving	bool	yes
checkpoint_every_size	byte	10,000 * log_page_size	10 * log_page_size	log_page_size
checkpoint_interval	msec	6min	1min	35,791,394min
checkpoint_sleep_msecs	msec	1	0
force_remove_log_archives	bool	yes
log_buffer_size	byte	128 * log_page_size	128 * log_page_size	INT_MAX * log_page_size
log_max_archives	int	INT_MAX	0	INT_MAX
log_trace_flush_time	int	0	0	INT_MAX
max_flush_size_per_second	byte	10,000 * db_page_size	1 * db_page_size	INT_MAX * db_page_size
remove_log_archive_interval_in_secs	sec	0	0
sync_on_flush_size	byte	200 * db_page_size	1 * db_page_size	INT_MAX * db_page_size

adaptive_flush_control

adaptive_flush_control is a parameter used automatically to adjust the flush capacity at every 50 ms depending on the current status of the flushing operation. The default value is yes. That is, this capacity is increased if a large number of INSERT or UPDATE operations are concentrated at a certain point of time and the number of flushed pages reaches the max_flush_size_per_second parameter value; and is decreased otherwise. In the same way, you can distribute the I/O load by adjusting the flush capacity on a regular basis depending on the workload.

background_archiving

background_archiving is a parameter used to create temporary archive logs periodically at a specific time. It is useful when balancing disk I/O load which has been caused by archiving logs. The default is yes.

checkpoint_every_size

checkpoint_every_size is a parameter to configure checkpoint interval by log page. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 10,000 * log_page_size (156.25M when log_page_size is 16K).

You can distribute disk I/O overload at the checkpoint by specifying lower size in the checkpoint_every_size parameter, especially in the environment where INSERT / UPDATE are heavily loaded at a specific time.

Checkpoint is a job to record every modified page in data buffers to database volumes (disk) at a specific point. Checkpoint can shrink a restore time after the database failure because this makes the transaction logs which have been generated previously than the checkpoint time needless when the restore is processed. However, an efficient checkpoint interval should be properly considered because this job can occur a lot of disk I/O.

Note

There are three ways to run checkpoint in CUBRID. The following two ways are provided by the setting of cubrid.conf.

checkpoint_interval: After the previous checkpoint is done, checkpoint is periodically processed at every time after the term of this parameter value.

checkpoint_every_size: Checkpoint is periodically processed at every time after the transaction log size of this parameter value.

If one condition on the above parameters is satisfied, checkpoint is processed.

The following one way is provided by a user's command.

If you run ";checkpoint" command in the CSQL interpreter, which is run with a "DBA" user, checkpoint is processed.

As a reference, if you run backup command during checkpoint, backup command is waited until checkpoint is ended.

checkpoint_interval

checkpoint_interval is a parameter to configure execution period of checkpoint. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied, and it is rounded up to seconds. For example, 1ms will be 1s, and 1001ms will be 2s. The default value is 6min and the minimum value is 1min.

checkpoint_sleep_msecs

checkpoint_sleep_msecs is a parameter to let the job which flushes a buffer's data into a disk process slowly. The default is 1 (millisecond).

force_remove_log_archives

force_remove_log_archives is a parameter to configure whether to allow the deletion of the files other than the recent log archive files whose number is specified by log_max_archives. The default value is yes.

If the value is set to yes, the files will be deleted other than the recent log archive files for which the number is specified by log_max_archives.

If it is set to no, the log archive files will not be deleted. Exceptionally, if ha_mode is set to on, the files other than the log archive files required for the HA-related processes and the recent log archive files of which the number is specified by log_max_archives will be deleted.

For setting up the CUBRID HA environment, see Environment Configuration.

log_buffer_size

log_buffer_size is a parameter to configure the size of log buffer to be cached in the memory. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 128 * log_page_size (2M when log_page_size is 16K).

If the value of the log_buffer_size parameter is large, performance can be improved (due to the decrease in disk I/O) in an environment where transactions are long and numerous. It is recommended to configure an appropriate value considering the memory size and operations of the system where CUBRID is installed.

Required memory size = the size of log buffer (log_buffer_size)

log_max_archives

log_max_archives is a parameter to configure the maximum number of archive log files. The minimum value is 0 and default value is INT_MAX (2,147,483,647). Its operations can differ depending on the configuration of force_remove_log_archives. For example, when log_max_archives is 3 and force_remove_log_archives is yes in the cubrid.conf file, the most recent three archive log files are recorded and when a fourth archiving log file is generated, the oldest archive log file is automatically deleted; the information about the deleted archive logs are recorded in the *_lginf file.

However, if an active transaction still refers to an existing archive log file, the archive log file will not be deleted. That is, if a transaction starts at the point that the first archive log file is generated, and it is still active until the fifth archive log is generated, the first archive log file cannot be deleted.

Also, if the information of archive logs is not applied to database volumes, these are not deleted. (Archive logs after which a checkpoint has occurred keep the information of modified pages of a data buffer; therefore, they are required to restore a database.)

If you change the value of log_max_archives dynamically during database operation, changed value will be applied when a new log archive file is created. For example, if you change this value from 10 to 5, old 5 files will be deleted when a new log archive file is created.

For setting up the CUBRID HA environment, see Environment Configuration.

Note

In 2008 R4.3 or lower and in 9.1, log_max_archives was also used for specifying the maximum number of keeping replication log files in HA environment. From 2008 R4.4 and 9.2, ha_copy_log_max_archives of cubrid_ha.conf is in charge of this role.

log_trace_flush_time

When the log flushing time takes more time than the time you set in this parameter, this event is recorded in the log of the database server.

The example of written information is as below.
03/18/14 10:20:45.889 - LOG_FLUSH_THREAD_WAIT
  total flush count: 1 page(s)
  total flush time: 310 ms
  time waiting for log writer: 308 ms
  last log writer info
    client: DBA@cdbs037.cub|copylogdb(15312)
    time spent by log writer: 308 ms
LOG_FLUSH_THREAD_WAIT: Event name

total flush count: The number of flushed pages when the event occurs

total flush time: Total spent time when the log is flushed

time waiting for log writer: The time LFT(Log Flushing Thread) has been waiting for LWT(Log Writer Thread)

last log writer info

DBA@cdbs037.cub|copylogdb(15312): copylogdb information related to LWT which let LFT wait <user@host_name|client_name(pid)>

time spent by log writer: Time spend by LWT measured in LWT; in general, this value is the same as the value of "time waiting for log writer")

max_flush_size_per_second

max_flush_size_per_second is a parameter to configure the maximum flush capacity when the flushing operation is performed from a buffer to a disk. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 10,000 * db_page_size (156.25M when db_page_size is 16K). That is, you can prevent concentration of I/O load at a certain point of time by configuring this parameter to control the maximum flush capacity per second.

If a large number of INSERT or UPDATE operations are concentrated at a certain point of time, and the flush capacity reaches the maximum capacity set by this parameter, only log pages are flushed to the disk, and data pages are no longer flushed. Therefore, you must set an appropriate value for this parameter considering the workload of the service environment.

remove_log_archive_interval_in_secs

Archive logs which exceed the specified number in the log_max_archives are removed when checkpoint occurs. By the way, many archive logs can be removed at once frequently after being piled up when jobs such as data migration or big data batch processing are performed. If files are removed at once like these, I/O overhead of database server is rapidly risen; therefore, we need to decrease this burden.

remove_log_archive_interval_in_secs parameter lets archive logs delete slowly to shrink this burden. The default is 0 (second). In the situations which jobs like big data batch processing occur frequently, it is recommended to set the deletion interval as a 60 seconds if the disk space is enough.

sync_on_flush_size

sync_on_flush_size is a parameter to configure the interval in pages between after data and log pages are flushed from buffer and before they are synchronized with FILE I/O of operating system. The default value is 200 * db_page_size (3.125M when db_page_size is 16K). That is, the CUBRID Server performs synchronization with the FILE I/O of the operating system whenever 200 pages have been flushed. This is also a parameter related to I/O load.

Transaction Processing-Related Parameters¶

The following are parameters for improving transaction commit performance. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
async_commit	bool	no
group_commit_interval_in_msecs	msec	0	0

async_commit

async_commit is a parameter used to activate the asynchronous commit functionality. If the parameter is set to no, which is the default value, the asynchronous commit is not performed; if it is set to yes, the asynchronous commit is executed. The asynchronous commit is a functionality that improves commit performance by completing the commit for the client before commit logs are flushed on the disk and having the log flush thread (LFT) perform log flushing in the background. Note that already committed transactions cannot be restored if a failure occurs on the database server before log flushing is performed.

group_commit_interval_in_msecs

group_commit_interval_in_msecs is a parameter to configure the interval (in milliseconds), at which the group commit is to be performed. If the parameter is configured to 0, which is the default value, the group commit is not performed. The group commit is a functionality that improves commit performance by combining multiple commits that occurred in the specified time period into a group so that commit logs are flushed on the disk at once.

Statement/Type-Related Parameters¶

The following are parameters related to SQL statements and data types supported by CUBRID. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
add_column_update_hard_default	bool	no
alter_table_change_type_strict	bool	no
ansi_quotes	bool	yes
block_ddl_statement	bool	no
block_nowhere_statement	bool	no
compat_numeric_division_scale	bool	no
default_week_format	int	0
group_concat_max_len	byte	1,024	4	33,554,432
intl_check_input_string	bool	no
intl_collation	string
intl_date_lang	string
intl_number_lang	string
no_backslash_escapes	bool	yes
only_full_group_by	bool	no
oracle_style_empty_string	bool	no
pipes_as_concat	bool	yes
plus_as_concat	bool	yes
require_like_escape_character	bool	no
return_null_on_function_errors	bool	no
string_max_size_bytes	byte	1,048,576	64	33,554,432
unicode_input_normalization	bool	no
unicode_output_normalization	bool	no

add_column_update_hard_default

add_column_update_hard_default is a parameter to configure whether or not to provide the hard default value as the input value for a column when you add a new column to the ALTER TABLE ... ADD COLUMN clause.

When there is NOT NULL constraint and no DEFAULT constraint, if a value of this parameter is set to yes, the value of newly added column will be inserted as hard default value; if it is set to no, CUBRID returns an error. For the hard default for each type, see the CHANGE/MODIFY Clauses of the ALTER TABLE statement.
SET SYSTEM PARAMETERS 'add_column_update_hard_default=yes';

CREATE TABLE tbl (i int);
INSERT INTO tbl VALUES (1),(2);
ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

SELECT * FROM tbl;
            i          j
=========================
            1          0
            2          0
SET SYSTEM PARAMETERS 'add_column_update_hard_default=no';

CREATE TABLE tbl (i INT);
INSERT INTO tbl VALUES (1),(2);
ALTER TABLE tbl ADD COLUMN j INT NOT NULL;
ERROR: Cannot add NOT NULL constraint for attribute "j": there are existing NULL values for this attribute.

alter_table_change_type_strict

alter_table_change_type_strict is a parameter to configure whether or not to allow the conversion of column values according to the type change, and the default value is no. If a value for this parameter is set to no, the value may be changed when you change the column types or when you add NOT NULL constraints; if it is set to yes, the value is not changed. For details, see CHANGE Clause in the CHANGE/MODIFY Clauses.

ansi_quotes

ansi_quotes is a parameter used to enclose symbols and character string to handle identifiers. The default value is yes. If this parameter value is set to yes, double quotations are handled as identifier symbols and single quotations are handled as character string symbols. If it is set to no, both double and single quotations are handled as character string symbols.

block_ddl_statement

block_ddl_statement is a parameter used to limit the execution of DDL (Data Definition Language) statements by the client. If the parameter is set to no, the given client is allowed to execute DDL statements. If it is set to yes, the client is not permitted to execute DDL statements. The default value is no.

block_nowhere_statement

block_nowhere_statement is a parameter used to limit the execution of UPDATE / DELETE statements without a condition clause (WHERE) by the client. If the parameter is set to no, the given client is allowed to execute UPDATE / DELETE statements without a condition clause. If it is set to yes, the client is not permitted to execute UPDATE / DELETE statements without a condition clause. The default value is no.

compat_numeric_division_scale

compat_numeric_division_scale is a parameter to configure the scale to be displayed in the result (quotient) of a division operation. If the parameter is set to no, the scale of the quotient is 9 if it is set to yes, the scale is determined by that of the operand. The default value is no.

default_week_format

default_week_format is a parameter to configure default value for the mode attribute of the WEEK() function. The default value is 0. For details, see WEEK().

intl_check_input_string

intl_check_input_string is a parameter to determine whether or not to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be terminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

group_concat_max_len

group_concat_max_len is a parameter used to limit the return value size of the GROUP_CONCAT() function. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 1,024. The minimum value is 4 and the maximum value is 33,554,432 bytes.

This function is affected by string_max_size_bytes parameter; if the value of group_concat_max_len is larger than the value string_max_size_bytes and the result size of GROUP_CONCAT exceeds the value of string_max_size_bytes, an error occurs.

intl_check_input_string

intl_check_input_string is a parameter to determine whether or not to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be terminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

intl_collation

intl_collation is a parameter which specifies a collation name about a specific application client. Specifying this parameter is the same as changing a collation of application client by using "SET NAMES" statement. Specifying a collation includes a charset.

The following two statements behave the same.
SET NAMES utf8;
SET SYSTEM PARAMETERS 'intl_collation=utf8_bin';
For an available value of intl_collation, see Collation.

intl_date_lang

intl_date_lang is a parameter used to input/output the values of TIME, DATE, DATETIME, and TIMESTAMP. If language name is omitted, it specifies a locale format of string of localized calendar (month, weekday, and AM/PM).

The values allowed are as follows: Note that to use all values, locale library should be configured except built-in locale. For configuring locale, see Locale Setting.

Language Locale Name of Language

English en_US

German de_DE

Spanish es_ES

French fr_FR

Italian it_IT

Japanese ja_JP

Cambodian km_KH

Korean ko_KR

Turkish tr_TR

Vietnamese vi_VN

Chinese zh_CN

Romanian ro_RO

The function recognizing input string based on calendar format of specified language is as follows:

TO_DATE()

TO_TIME()

TO_DATETIME()

TO_TIMESTAMP()

STR_TO_DATE()

The function outputting string based on calendar format of specified language is as follows:

TO_CHAR()

DATE_FORMAT()

TIME_FORMAT()

intl_number_lang

intl_number_lang is a parameter used to specify locale applied when numeric format is assigned to input/output string in the function where a string is converted to number or number is converted to string. A delimiter and decimal symbol are used for numeric localization. In general, a comma and period are used; however, it can be changeable based on locale. For example, while number 1000.12 is written as 1,000.12 in most locale, it is written as 1.000,12 in tr_TR locale.

The function recognizing input string based on calendar format of specified language is as follows:

TO_NUMBER()

The function outputting string based on calendar format of specified language is as follows:

FORMAT()

TO_CHAR()

no_backslash_escapes

no_backslash_escapes is a parameter to configure whether or not to use backslash (\) as an escape character, and the default value is yes. If a value for this parameter is set to no, backslash (\) will be used as an escape character; if it is set to yes, backslash (\) will be used as a normal character. For example, if this value is set to no, "\n" means a newline character. For details, see Escape Special Characters.

only_full_group_by

only_full_group_by is a parameter to configure whether or not to use extended syntax about using GROUP BY statement.

If this parameter value is set to no, an extended syntax is applied thus, a column that is not specified in the GROUP BY statement can be specified in the SELECT column list. If it is set to yes, a column that is only specified in the GROUP BY statement can be the SELECT column list.

The default value is no. Therefore, specify the only_full_group_by parameter value to yes to execute queries by SQL standards. Because the extended syntax is not applied in this case, an error below is displayed.
ERROR: Attributes exposed in aggregate queries must also appear in the group by clause.

oracle_style_empty_string

oracle_style_empty_string is a parameter used to improve compatibility with other DBMS (Database Management Systems) and specifies whether or not to process empty strings as NULL as in Oracle DBMS. If the oracle_style_empty_string parameter is set to no, the character string is processed as a valid string if it is set to yes, the empty string is processed as NULL.

pipes_as_concat

pipes_as_concat is a parameter to configure how to handle a double pipe symbol. The default value is yes. If this parameter value is set to yes, a double pipe symbol is handled as a concatenation operator if no, it is handled as the OR operator.

plus_as_concat

plus_as_concat is a parameter to configure the plus (+) operator, and the default value is yes. If a value for this parameter is set to yes, the plus (+) operator will be interpreted as a concatenation operator; if it is set to no, the operator will be interpreted as a numeric operator.
-- plus_as_concat = yes
SELECT '1'+'1';
         '1'+'1'
======================
         '11'
SELECT '1'+'a';
         '1'+'a'
======================
         '1a'
-- plus_as_concat = no
SELECT '1'+'1';
                '1'+'1'
==========================
 2.000000000000000e+000
SELECT '1'+'a';
ERROR: Cannot coerce 'a' to type double.

require_like_escape_character

require_like_escape_character is parameter to configure whether or not to use an ESCAPE character in the LIKE clause, and the default value is no. If a value for this parameter is set to yes and a value for no_backslash_escapes is set to no, backslash (\) will be used as an ESCAPE character in the strings of the LIKE clause, otherwise you should specify an ESCAPE character by using the LIKE ... ESCAPE clause. For details, see LIKE.

return_null_on_function_errors

return_null_on_function_errors is a parameter used to define actions when errors occur in some SQL functions, and the default value is no. If a value for this parameter is set to yes, NULL is returned; if it is set to no, an error is returned when the error occurs in functions, and the related message is displayed.

The following SQL functions are affected by this system parameter.

Date/Time functions

ADDDATE()

ADDTIME()

DATEDIFF()

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

FROM_DAYS()

FROM_UNIXTIME()

HOUR()

LAST_DAY()

MAKEDATE()

MAKETIME()

MINUTE()

MONTH()

QUARTER()

SEC_TO_TIME()

SECOND()

TIME()

TIME_TO_SEC()

TIMEDIFF()

TO_DAYS()

WEEK()

WEEKDAY()

YEAR()

String functions

ASCII()

BIN()

BIT_LENGTH()

CHR()

Numeric functions

ABS()

ACOS()

ASIN()

ATAN()

ATAN2()

CEIL()

CONV()

COS()

COT()

DEGREES()

EXP()

FLOOR()

LN()

LOG2()

LOG10()

MOD()

POW()

RADIANS()

SIGN()

SIN()

SQRT()

TAN()

TRUNC()

WIDTH_BUCKET()
SET SYSTEM PARAMETERS 'return_null_on_function_errors=no';
SELECT YEAR('12:34:56');
ERROR: Conversion error in time format.
SET SYSTEM PARAMETERS 'return_null_on_function_errors=yes';
SELECT YEAR('12:34:56');
   year('12:34:56')
======================
   NULL

string_max_size_bytes

string_max_size_bytes is a parameter to define the maximum byte allowable in string functions or operators. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 1,048,576(1M). The minimum value is 64 and the maximum value is 33,554,432(32M).

The functions and operators affected by this parameter are as follows:

SPACE()

CONCAT()

CONCAT_WS()

'+': Operand of string

REPEAT()

GROUP_CONCAT(): This function is affected not only by string_max_size_bytes parameter, but also by group_concat_max_len.

INSERT() function

unicode_input_normalization

unicode_input_normalization is a parameter to determine whether or not to input unicode stored in system level or not. The default value is no.

In general, unicode text can be stored in "fully composed" or "fully decomposed". When character 'Ä' has 00C4 (if it is encoded in UTF-8, it becomes 2 bytes of C3 84) which is only one code point. In "fully decomposed" mode, it has two code points/characters. It is 0041 (character "A") and 0308(COMBINING DIAERESIS). In case of UTF-8 encoding, it becomes 3 bytes of 41 CC 88.

CUBRID can work with fully composed unicode. For clients which have fully decomposed texts, configure the value of unicode_input_normalization to yes so that it can be converted to fully composed mode; and then it can be reverted to fully decomposed mode. For normalization of unicode encapsulation of CUBRID, compatibility equivalence is not applied. In general, normalization of unicode is not possible to revert after composition, CUBRID supports revert for characters an many as possible, and it applies normalization of unicode encapsulation. The characteristics of CUBRID normalization are as follows:

In case of language specific, normalization does not depend on locale.

If one or more locale can be used, this means every CAS/CSQL process, not CUBRID server. The unicode_input_normalization system parameter determines whether composition of input codes by normalization in system level. The unicode_output_normalization system parameter determines whether composition of output codes by normalization in system level.

Collation and normalization does not have direct relationship.

Even though the value of unicode_input_normalization is no, the string of extensible collation (utf8_de_exp, utf8_jap_exp, utf8_km_exp) is properly sorted fully decomposed mode, it is not intended; it is side-effect of UCA(Unicode Collation Algorithm). The extensible collation is implemented only with fully composed texts.

In CUBRID, composition and decomposition for normalization does not work separately.

It is generally used when unicode_input_normalization and unicode_output_normalization are yes. In this case, codes entered from clients are stored in composed mode and output in decomposed mode.

If the application client sends the decomposed text data into CUBRID, let CUBRID deal with the composed code, by setting unicode_input_normalization as yes.

If the application client can deal with the decomposed text data only, let CUBRID always send the decomposed code, by setting unicode_output_normalization as yes.

If the application client knows both of input and output, leave the setting unicode_input_normalization and unicode_output_normalization as no.

For more details, see Globalization.

unicode_output_normalization

unicode_output_normalization is a parameter to determine whether or not to output unicode stored in system level. The default value is no. For details, see the above unicode_input_normalization description.

Query Plan Cache-Related Parameters¶

The following are parameters related to the query plan cache functionality. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
max_plan_cache_entries	int	1,000
max_filter_pred_cache_entries	in	1,000

max_plan_cache_entries

max_plan_cache_entries is a parameter to configure the maximum number of query plans to be cached in the memory. If the max_plan_cache_entries parameter is configured to -1 or 0, generated query plans are not stored in the memory cache; if it is configured to an integer value equal to or greater than 1, a specified number of query plans are cached in the memory.

The following example shows how to cache up to 1,000 queries.
max_plan_cache_entries=1000

max_filter_pred_cache_entries

max_filter_pred_cache_entries is a parameter used to specify the maximum number of filtered index expressions. The filtered index expressions are stored with them complied and can be immediately used in server. If it is not stored in cache, the process is required which filtered index expressions are fetched from database schema and interpreted.

Utility-Related Parameters¶

The following are parameters related to utilities used in CUBRID. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
backup_volume_max_size_bytes	byte	0	32K
communication_histogram	bool	no
compactdb_page_reclaim_only	int	0
csql_history_num	int	50	1	200

backup_volume_max_size_bytes

backup_volume_max_size_bytes is a parameter to configure the size of the backup volume file created by the cubrid backupdb utility in byte unit. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 0, and the minimum value is 32K.

If the parameter is configured to 0, which is the default value, the created backup volume is not partitioned; if it is configured to a value larger than 0, the backup volume is partitioned as much as it is specified size.

communication_histogram

communication_histogram is a parameter associated with Session Commands ";.h" of the CSQL Interpreter and the default value is no. For details, see Dumping CSQL execution statistics information.

compactdb_page_reclaim_only

compactdb_page_reclaim_only is a parameter to configure the compactdb utility, which compacts the storage of already deleted objects to reuse OIDs of the already assigned storage. Storage optimization with the compactdb utility can be divided into three steps. The optimization steps can be selected through the compactdb_page_reclaim_only parameter. If the parameter is configured to 0, which is the default value, step 1, 2 and 3 are all performed, so the storage is optimized in data, table and file units. If it is configured to 1, step 1 is skipped to have the storage optimized in table and file units. If it is configured to 2, steps 1 and 2 are skipped to have the storage optimized only in file units.

Step 1: Optimizes the storage only in data unit.

Step 2: Optimizes the storage in table unit.

Step 3: Optimizes the storage in file (heap file) unit.

csql_history_num

csql_history_num is a parameter to configure the CSQL Interpreter and the number of SQL statements to be stored in the history of the CSQL Interpreter. The default value is 50.

HA-Related Parameters¶

The following are HA-related parameters. The type and value range for each parameter are as follows:

Parameter Name	Type	Default Value
ha_mode	string	off

ha_mode

The ha_mode parameter is used to set CUBRID HA, and the default value is off.

off : CUBRID HA is not used.

on : CUBRID HA is used using the configured node as a node for failover.

replica : CUBRID HA is used without using the configured node as a node for failover.

To use the CUBRID HA feature, you should set HA-related parameters in the cubrid_ha.conf file in addition to the ha_mode parameter. For details, see CUBRID HA.

Other Parameters¶

The following are other parameters. The type and value range for each parameter are as follows:

Parameter Name	Type	Default	Min	Max
access_ip_control	bool	no
access_ip_control_file	string
agg_hash_respect_order	bool	yes
auto_restart_server	bool	yes
index_scan_in_oid_order	bool	no
index_unfill_factor	float	0.05	0	0.5
java_stored_procedure	bool	no
multi_range_optimization_limit	int	100	0	10,000
optimizer_enable_merge_join	bool	no
pthread_scope_process	bool	yes
server	string
service	string
session_state_timeout	sec	21,600 (6 hours)	60(1 minute)	31,536,000 (1 year)
sort_limit_max_count	int	1000	0	INT_MAX
sql_trace_slow	msec	-1(inf)	0	86,400,000 (24 hours)
sql_trace_execution_plan	bool	no
use_orderby_sort_limit	bool	yes

access_ip_control

access_ip_control is a parameter to configure whether to use feature limiting the IP addresses that allow server access. The default value is no. For details, see Limiting Database Server Access.

access_ip_control_file

access_ip_control_file is a parameter to configure the file name in which the list of IP addresses allowed by servers is stored. If access_ip_control value is set to yes, database server allows the list of IP addresses only stored in the file specified by this parameter. For details, see Limiting Database Server Access.

agg_hash_respect_order

agg_hash_respect_order is a parameter to configure whether the groups in an aggregate function will be returned ordered or not. The default is yes. As a reference, see max_agg_hash_size.

If all the groups (keys and accumulators) can fit into hash memory, then "agg_hash_respect_order=no" will skip sorting them before writing to output, so it is fair to assume that the order cannot be guaranteed in this case. However, when overflows occur, then a sort step must be performed and you will get the results in-order even with "agg_hash_respect_order=no".

auto_restart_server

auto_restart_server is a parameter to configure whether to restart the process when it stops due to fatal errors being occurred in database server process. If auto_restart_server value is set to yes, the server process automatically restarts when it has stopped due to errors; it does not restart in case it stops by following normal process (by using STOP command).

index_scan_in_oid_order

index_scan_in_oid_order is a parameter to configure the result data to be retrieved in OID order after the index scan. If the parameter is set to no, which is the default value, results are retrieved in data order; if it is set to yes, they are retrieved in OID order.

index_unfill_factor

If there is no free space because index pages are full when the INSERT or UPDATE operation is executed after the first index is created, the split of index page nodes occurs. This substantially affects the performance by increasing the operation time. index_unfill_factor is a parameter to configure the percent of free space defined for each index page node when an index is created. The index_unfill_factor value is applied only when an index is created for the first time. The percent of free space defined for the page is not maintained dynamically. Its value ranges between 0 and 0.5. The default value is 0.05.

If an index is created without any free space for the index page node (index_unfill_factor is set to 0), the split of index page nodes occurs every time an additional insertion is made. This may degrade the performance.

If the value of index_unfill_factor is large, a large amount of free space is available when an index is created. Therefore, better performance can be obtained because the split of index nodes does not occur for a relatively long period of time until the free space for the nodes is filled after the first index is created.

If this value is small, the amount of free space for the nodes is small when an index is created. Therefore, it is likely that the index nodes are spilt by INSERT or UPDATE because free space for the index nodes is filled in a short period of time.

java_stored_procedure

java_stored_procedure is a parameter to configure whether to use Java stored procedures by running the Java Virtual Machine (JVM). If the parameter is set to no, which is the default value, JVM is not executed; if it is set to yes, JVM is executed so you can use Java stored procedures. Therefore, configure the parameter to yes if you plan to use Java stored procedures.

multi_range_optimization_limit

If the number of rows specified by the LIMIT clause in the query, which has multiple ranges (col IN (?, ?, ... ,?)) and is available to use an index, is within the number specified in the multi_range_optimization_limit parameter, the optimization for the way of index sorting will be performed. The default value is 100.

For example, if a value for this parameter is set to 50, LIMIT 10 means that it is within the value specified by this parameter, so that the values that meet the conditions will be sorted to produce the result. If LIMIT is 60, it means that it exceeds the parameter configuration value, so that it gets and sorts out all values that meet the conditions.

Depending on the setting value, the differences are made between collecting the result with on-the-fly sorting of the intermediate values and sorting the result values after collecting them, and the bigger value could make more unfavorable performance.

optimizer_enable_merge_join

optimizer_enable_merge_join is a parameter to specify whether to include sort merge join plan as a candidate of query plans or not. The default is no. Regarding sort merge join, see Using SQL Hint.

pthread_scope_process

pthread_scope_process is a parameter to configure the contention scope of threads. It only applies to AIX systems. If the parameter is set to no, the contention scope becomes PTHREAD_SCOPE_SYSTEM; if it is set to yes, it becomes PTHREAD_SCOPE_PROCESS. The default value is yes.

server

server is a parameter used to register the name of database server process which will run automatically when CUBRID server starts.

service

service is a parameter to configure process that starts automatically when the CUBRID service starts. There are four types of processes: server, broker, manager, and heartbeat. Three processes are usually registered as in service=server,broker,manager.

If the parameter is set to server, the database process specified by the @server parameter gets started.

If the parameter is set to broker, the broker process gets started.

If the parameter is set to manager, the manager process gets started.

If the parameter is set to heartbeat, the HA-related processes get started.

session_state_timeout

session_state_timeout is a parameter used to define how long the CUBRID session data will be kept. The session data will be deleted when the driver terminates the connection or the session time expires. The session time will expire after the specified time if a client terminates abnormally.

Custom variables defined by SET and PREPARE statements can be deleted by DROP / DEALLOCATE statements before session timeout.

The default value is 21,600 seconds(6 hours), and this parameter's unit is second.

sort_limit_max_count

Regarding SORT-LIMIT optimization which can be applied when top-N rows are sorted by "ORDER BY ... LIMIT N" statement, this parameter specifies the LIMIT count to limit applying this optimization. When the value of N is smaller than a value of sort_limit_max_count, SORT-LIMIT optimization is applied. The default is 1000, the minimum is 0(which means that this optimization is disabled), and the maximum is INT_MAX.

For more details, see SORT-LIMIT optimization.

sql_trace_slow

sql_trace_slow is a parameter to configure the execution time of a query which will be judged as a long time execution. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is -1 and the maximum value is 86,400,000 msec (24 hour). -1 means that the infinite time, so any queries will not be judged as a long duration query. For details, see the below sql_trace_execution_plan.

Note

The system parameter sql_trace_slow judges the query execution time based on the server, but the broker parameter MAX_QUERY_TIMEOUT judges the query execution time based on the broker.

sql_trace_execution_plan

sql_trace_execution_plan is a parameter to configure if the query plan of the long running query is written to the log or not. The default value is no.

If it is set to yes, a long running SQL, a query plan and the output of cubrid statdump command are written to the server error log file(located on $CUBRID/log/server directory) and CAS log file(located on $CUBRID/log/broker/sql_log directory) .

If it is set to no, only a long running SQL is written to the server error log file and CAS log file, and this SQL is displayed when you execute cubrid statdump command.

For example, if you want to write the execution plan of the slow query to the log file, and specify the query which executes more than 5 seconds as the slow query, then configure the value of the sql_trace_slow parameter as 5000(ms) and configure the value of the sql_trace_execution_plan parameter as yes.

But, on the server error log file, the related information is written only when the value of error_log_level is NOTIFICATION.

use_orderby_sort_limit

use_orderby_sort_limit is a parameter to configure whether to keep the intermediate result of sorting and merging process in the statement including the ORDER BY ... LIMIT row_count clause as many as row_count. If it is set to yes, you can decrease unnecessary comparing and merging processes because as many as intermediate results will be kept as the value of row_count. The default value is yes.

Broker Configuration¶

cubrid_broker.conf Configuration File and Default Parameters¶

Broker System Parameters

The following table shows the broker parameters available in the broker configuration file (cubrid_broker.conf). For details, see Common Parameters and Parameter by Broker. You can temporarily change the parameter of which configuration values can be dynamically changed by using the broker_changer utility. To apply configuration values even after restarting all brokers with cubrid broker restart, you should change the values in the cubrid_broker.conf file.

Category Use Parameter Name Type Default Value Dynamic Changes

Common Parameters Access ACCESS_CONTROL bool no

ACCESS_CONTROL_FILE string

Logging ADMIN_LOG_FILE string log/broker/cubrid_broker.log

Broker server (cub_broker) MASTER_SHM_ID int 30,001

Parameter by Broker Access ACCESS_LIST string

ACCESS_MODE string RW available

BROKER_PORT int 30,000(max : 65,535)

CONNECT_ORDER string SEQ available

ENABLE_MONITOR_HANG string OFF

KEEP_CONNECTION string AUTO available

MAX_NUM_DELAYED_HOSTS_LOOKUP int -1

PREFERRED_HOSTS string available

RECONNECT_TIME sec 600 available

REPLICA_ONLY string OFF

Broker App. Server(CAS) APPL_SERVER_MAX_SIZE MB Windows 32bit: 40, Windows 64bit: 80, Linux: 0(max: 2,097,151) available

APPL_SERVER_MAX_SIZE_HARD_LIMIT MB 1,024 available

APPL_SERVER_PORT int BROKER_PORT+1

APPL_SERVER_SHM_ID int 30,000

AUTO_ADD_APPL_SERVER string ON

MAX_NUM_APPL_SERVER int 40

MIN_NUM_APPL_SERVER int 5

TIME_TO_KILL sec 120 available

Transaction & Query CCI_DEFAULT_AUTOCOMMIT string ON

LONG_QUERY_TIME sec 60(max: 86,400) available

LONG_TRANSACTION_TIME sec 60(max: 86,400) available

MAX_PREPARED_STMT_COUNT int 2,000(min: 1) available

MAX_QUERY_TIMEOUT sec 0(max: 86,400) available

SESSION_TIMEOUT sec 300 available

STATEMENT_POOLING string ON available

TRIGGER_ACTION string ON available

Logging ACCESS_LOG string OFF available

ACCESS_LOG_DIR string log/broker

ACCESS_LOG_MAX_SIZE KB 10M(max: 2G) available

ERROR_LOG_DIR string log/broker/error_log available

LOG_DIR string log/broker/sql_log available

SLOW_LOG string ON available

SLOW_LOG_DIR string log/broker/sql_log available

SQL_LOG string ON available

SQL_LOG_MAX_SIZE KB 10,000 available

SHARD SHARD string OFF

SHARD_CONNECTION_FILE string shard_connection.txt

SHARD_DB_NAME string

SHARD_DB_PASSWORD string

SHARD_DB_USER string

SHARD_IGNORE_HINT string OFF

SHARD_KEY_FILE string shard_key.txt

SHARD_KEY_FUNCTION_NAME string

SHARD_KEY_LIBRARY_NAME string

SHARD_KEY_MODULAR int 256

SHARD_MAX_CLIENTS int 256

SHARD_MAX_PREPARED_STMT_COUNT int 10,000

SHARD_NUM_PROXY int 1

SHARD_PROXY_CONN_WAIT_TIMEOUT sec 8h

SHARD_PROXY_LOG string ERROR available

SHARD_PROXY_LOG_DIR string log/broker/proxy_log

SHARD_PROXY_LOG_MAX_SIZE KB 100,000 available

SHARD_PROXY_SHM_ID int

SHARD_PROXY_TIMEOUT sec 30(sec)

Etc MAX_STRING_LENGTH int -1

SERVICE string ON

SOURCE_ENV string cubrid.env

Default Parameters

The cubrid_broker.conf file, the default broker configuration file created when installing CUBRID, includes some parameters that must be modified by default. If you want to modify the values of parameters that are not included in the configuration file by default, you can add or modify one yourself.

The following is the content of the cubrid_broker.conf file provided by default.
[broker]
MASTER_SHM_ID           =30001
ADMIN_LOG_FILE          =log/broker/cubrid_broker.log

[%query_editor]
SERVICE                 =ON
BROKER_PORT             =30000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =30000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

[%BROKER1]
SERVICE                 =ON
BROKER_PORT             =33000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =33000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

Broker Configuration File Related Environment Variables

You can specify the location of broker configuration file (cubrid_broker.conf) file by using the CUBRID_BROKER_CONF_FILE variable. The variable is used when executing several brokers with different configuration.

Common Parameters¶

The following are parameters commonly applied to entire brokers; it is written under [broker] section.

Access¶

ACCESS_CONTROL

ACCESS_CONTROL is a parameter used to limit applications which are trying to connect a broker. The default value is OFF. For details, see Limiting Broker Access.

ACCESS_CONTROL_FILE

ACCESS_CONTROL_FILE is a parameter to configure the name of a file in which a database name, database user ID, and the list of IPs are stored. List of IPs can be written up to the maximum of 256 lines per <db_name>:<db_user> in a broker. For details, see Limiting Broker Access.

Logging¶

ADMIN_LOG_FILE

ADMIN_LOG_FILE is a parameter to configure the file in which time of running CUBRID broker is stored. The default value is a log/broker/cubrid_broker.log file.

Broker Server(cub_broker)¶

MASTER_SHM_ID

MASTER_SHM_ID is a parameter used to specify the identifier of shared memory which is used to manage the CUBRID broker. Its value must be unique in the system. The default value is 30001.

Parameter by Broker¶

The following describes parameters to configure the environment variables of brokers; each parameter is located under [%broker_name]. The maximum length of broker_name is 63 characters in English.

Access¶

ACCESS_LIST

ACCESS_LIST is a parameter to configure the name of a file where the list of IP addresses of an application which allows access to the CUBRID broker is stored. To allow access by IP addresses access 210.192.33.* and 210.194.34.*, store them to a file (ip_lists.txt) and then assign the file name with the value of this parameter.

ACCESS_MODE

ACCESS_MODE is a parameter to configure default mode of the broker. The default value is RW. For details, see cubrid_broker.conf.

BROKER_PORT

BROKER_PORT is a parameter to configure the port number of the broker; the value must be unique and smaller than 65,535. The default port value of query_editor' broker is 30,000 and the port value of the broker1 is 33,000.

CONNECT_ORDER

CONNECT_ORDER is a parameter to specify whether a CAS tries to connect to one of hosts in the order or randomly in $CUBRID_DATABASES/databases.txt, when a CAS decides the order of connecting to a host. The default is SEQ; a CAS tries to connect to a host in the order. if this value is RANDOM, a CAS tries to connect to a host randomly. If PREFERRED_HOSTS parameter is specified, firstly a CAS tries to connect to one of hosts specified in PREFERRED_HOSTS, then uses db-host values in $CUBRID_DATABASES/databases.txt only when the connection is failed.

ENABLE_MONITOR_HANG

ENABLE_MONITOR_HANG is a parameter to configure whether to block the access from the application to the broker or not, when more than a certain ratio of CASes on that broker are hung. If the ENABLE_MONITOR_HANG parameter value is ON, blocking feature is processed. The default value is OFF. If its OFF, don't do the behavior.

The broker process judges the CAS as hung if the hanging status of the CAS keeps more than one minute, then block the access from applications to that broker; it brings the behavior which the applications try to access to the alternative hosts(altHosts) configured by the connection URL.

KEEP_CONNECTION

KEEP_CONNECTION is a parameter to configure the way of connection between CAS and application clients; it is set to one of the following: ON or AUTO. If this value is ON, it is connected in connection unit. If it is AUTO and the number of servers is more than that of clients, transaction unit is used; in the reverse case, connection unit is used. The default value is AUTO.

MAX_NUM_DELAYED_HOSTS_LOOKUP

When almost all DB servers have the delay of replication in the HA environment where multiple DB servers on db-host of databases.txt are specified, check if the connection is established or not until the number of delayed replication servers; the number is specified in MAX_NUM_DELAYED_HOSTS_LOOKUP (whether the delay of replication in the DB server is judged only with the standby hosts; it is determined by the setting of ref:ha_delay_limit <ha_delay_limit>). See MAX_NUM_DELAYED_HOSTS_LOOKUP for further information.

PREFERRED_HOSTS

PREFERRED_HOSTS is a parameter to specify the order of a host to which a CAS tries to connect in a first priority. If the connection is failed after trying connection in the order specified in PREFERRED_HOSTS, a CAS tries to connect to the one of hosts specified in $CUBRID_DATABASES/databases.txt. The default value is NULL. For details, see cubrid_broker.conf.

RECONNECT_TIME

If the time specified by RECONNECT_TIME is elapsed in a certain status, CAS will try to reconnect to the other DB server. The default of this parameter is 600s(10min). You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied.

a certain status which CAS tries to reconnect is as follows.

when CAS is connected to not a DB server in PREFERRED_HOSTS, but the DB server of db-host in databases.txt.

when CAS with "ACCESS_MODE=RO"(Read Only) is connected to not the standby DB server, but the active DB server.

when CAS is connected to the DB server of which replication is delayed.

When RECONNECT_TIME is 0, CAS does not try to reconnect.

REPLICA_ONLY

If a value of REPLICA_ONLY is ON, CAS is connected only to replicas. The default is OFF. Even though the value of REPLICA_ONLY is ON, when a value of ACCESS_MODE is RW, it is possible to write directly to the replica DB. However, the data to be written directly to the replica DB are not replicated.

Note

Please note that replication mismatch occurs when you write the data directly to the replica DB.

Broker App. Server(CAS)¶

APPL_SERVER_MAX_SIZE

APPL_SERVER_MAX_SIZE is a parameter to configure the maximum size of the process memory usage handled by CAS. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB), megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied.

Specifying this parameter makes transactions terminate (commit or rollback) only when it is executed by a user. In contrast to this, specifying APPL_SERVER_MAX_SIZE_HARD_LIMIT makes transactions forcibly terminate (rollback) and restart CAS.

Note that the default values of Windows and Linux are different from each other.

For 32-bit Windows, the default value is 40 MB; for 64-bit Windows, it is 80 MB. The maximum value is the same on Windows and Linux as 2,097,151 MB. When current process size exceeds the value of APPL_SERVER_MAX_SIZE, broker restarts the corresponding CAS.

For Linux, the default value of APPL_SERVER_MAX_SIZE is 0; CAS restarts in the following conditions.

APPL_SERVER_MAX_SIZE is zero or negative: At the point when current process size becomes twice as large as initial memory

APPL_SERVER_MAX_SIZE is positive: At the point when it exceeds the value specified in APPL_SERVER_MAX_SIZE

Note

Be careful not to make the value too small because application servers may restart frequently and unexpectedly. In general, the value of APPL_SERVER_MAX_SIZE_HARD_LIMIT is greater than that of APPL_SERVER_MAX_SIZE. For details, see description of APPL_SERVER_MAX_SIZE_HARD_LIMIT.

APPL_SERVER_MAX_SIZE_HARD_LIMIT

APPL_SERVER_MAX_SIZE_HARD_LIMIT is a parameter to configure the maximum size of process memory usage handled by CAS. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB), megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied. The default value is 1,024 (MB), and the maximum value is 2,097,151 (MB).

Specifying this parameter makes transactions being processed forcibly terminate (rollback) and restart CAS. In contrast to this, specifying APPL_SERVER_MAX_SIZE makes transactions terminate only when it is executed by a user.

Note

Be careful not to make the value too small because application servers may restart frequently and unexpectedly. When restarting CAS, APPL_SERVER_MAX_SIZE is specified to wait for normal termination of transactions although memory usage increases; APPL_SERVER_MAX_SIZE_HARD_LIMIT is specified to forcibly terminate transactions if memory usage exceeds the maximum value allowed. Therefore, in general, the value of APPL_SERVER_MAX_SIZE_HARD_LIMIT is greater than that of APPL_SERVER_MAX_SIZE.

APPL_SERVER_PORT

APPL_SERVER_PORT is a parameter to configure the connection port of CAS that communicates with application clients; it is used only in Windows. In Linux, the application clients and CAS use the UNIX domain socket for communication; therefore, APPL_SERVER_PORT is not used. The default value is determined by adding plus 1 to the BROKER_PORT parameter value. The number of ports used is the same as the number of CAS, starting from the specified port's number plus 1. For example, when the value of BROKER_PORT is 30,000 and the APPL_SERVER_PORT parameter value has been configured, and if the MIN_NUM_APPL_SERVER value is 5, five CASes uses the ports numbering between 30,001 and 30,005, respectively. The maximum number of CAS specified in the MAX_NUM_APPL_SERVER parameter in cubrid_broker_conf; therefore, the maximum number of connection ports is also determined by the value of MAX_NUM_APPL_SERVER parameter.

On the Windows system, if a firewall exists between an application and a CUBRID broker, the communication port specified in BROKER_PORT and APPL_SERVER_PORT must be opened.

Note

For the CUBRID_TMP environment variable that specifies the UNIX domain socket file path of cub_master and cub_broker processes, see Configuring Environment Variables.

APPL_SERVER_SHM_ID

APPL_SERVER_SHM_ID is a parameter to configure the ID of shared memory used by CAS; the value must be unique within system. The default value is the same as the port value of the broker.

AUTO_ADD_APPL_SERVER

AUTO_ADD_APPL_SERVER is a parameter to configure whether CAS increase automatically to the value specified in MAX_NUM_APPL_SERVER in case of needed; the value will be either ON or OFF (default: ON).

MAX_NUM_APPL_SERVER

MAX_NUM_APPL_SERVER is a parameter to configure the maximum number of simultaneous connections allowed. The default value is 40.

MIN_NUM_APPL_SERVER

MIN_NUM_APPL_SERVER is a parameter to configure the minimum number of CAS even if any request to connect the broker has not been made. The default value is 5.

TIME_TO_KILL

TIME_TO_KILL is a parameter to configure the time to remove CAS in idle state among CAS added dynamically. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied. The default value is 120 (seconds).

An idle state is one in which the server is not involved in any jobs. If this state continues exceeding the value specified in TIME_TO_KILL, CAS is removed.

The value configured in this parameter affects only CAS added dynamically, so it applies only when the AUTO_ADD_APPL_SERVER parameter is configured to ON. Note that times to add or remove CAS will be increased more if the TIME_TO_KILL value is so small.

Transaction & Query¶

CCI_DEFAULT_AUTOCOMMIT

CCI_DEFAULT_AUTOCOMMIT is a parameter to configure whether to make application implemented in CCI interface or CCI-based interface such as PHP, OLE DB, Perl, Python, and Ruby commit automatically. The default value is ON. This parameter does not affect applications implemented in JDBC.

If the CCI_DEFAULT_AUTOCOMMIT parameter value is OFF, the broker application server (CAS) process is occupied until the transaction is terminated. Therefore, it is recommended to execute commit after completing fetch when executing the SELECT statement.

Note

The CCI_DEFAULT_AUTOCOMMIT parameter has been supported from 2008 R4.0, and the default value is OFF for the version. Therefore, if you use CUBRID 2008 R4.1 or later versions and want to keep the configuration OFF, you should manually change it to OFF to avoid auto-commit of unexpected transaction.

Warning

In ODBC driver, the setting of CCI_DEFAULT_AUTOCOMMIT is ignored and worked as ON; therefore, you should set the autocommit on or off in the program directly.

LONG_QUERY_TIME

LONG_QUERY_TIME is a parameter to configure execution time of query which is evaluated as long-duration query. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 60 (seconds) and the maximum value is 86,400(1 day).

This value can be valued in milliseconds with a decimal separator. For example, the value can be configured into 0.5 to configure 500 msec.

Note that if a parameter value is configured to 0, a long-duration query is not evaluated.

LONG_TRANSACTION_TIME

LONG_TRANSACTION_TIME is a parameter to configure execution time of query which is evaluated as long-duration transaction. The default value is 60 (seconds) and the maximum value is 86,400(1 day).

This value can be valued in milliseconds with a decimal separator. For example, the value can be configured into 0.5 to configure 500 msec.

Note that if a parameter value is configured to 0, a long-duration transaction is not evaluated.

MAX_PREPARED_STMT_COUNT

MAX_PREPARED_STMT_COUNT is a parameter used to limit the number of prepared statements by user (application) access. The default value is 2,000 and the minimum value is 1. The problem in which prepared statement exceeding allowed memory is mistakenly generated by system can be prohibited by making users specify the parameter value.

Note

When you want to change the value of MAX_PREPARED_STMT_COUNT dynamically by broker_changer command, this can be changed only when this is bigger than the existing value; this cannot be changed when this is smaller than the existing value.

MAX_QUERY_TIMEOUT

MAX_ QUERY_TIMEOUT is a parameter to configure timeout value of query execution. When time exceeds a value specified in this parameter after starting query execution, the query being executed stops and rolls back. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 0 (seconds) and it means infinite waiting. The value range is available from 0 to 86,400 seconds(one day).

The smallest value (except 0) between the MAX_QUERY_TIMEOUT value and query timeout value of an application is applied if query timeout is configured in an application.

Note

See the cci_connect_with_url() and cci_set_query_timeout() functions to configure query timeout of CCI applications. For configuring query timeout of JDBC applications, see the setQueryTimeout method.

SESSION_TIMEOUT

SESSION_TIMEOUT is a parameter to configure timeout value for the session of the broker. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is 300 (seconds).

If there is no response to the job request for the specified time period, session will be terminated. If a value exceeds the value specified in this parameter without any action taken after starting transaction, the connections are terminated.

STATEMENT_POOLING

STATEMENT_POOLING is a parameter to configure whether to use statement pool feature. The default value is ON.

CUBRID closes all handles of prepared statement in the corresponding client sessions when transaction commit or rollback is made. If the value of STATEMENT_POOLING is set to ON, the handles are reusable because they are maintained in the pool. Therefore, in an environment where libraries, such as general applications reusing prepared statement or DBCP where statement pooling is implemented is applied, the default configuration (ON) should be maintained.

If the prepared statement is executed after transaction commit or termination while STATEMENT_POOLING is set to OFF, the following message will be displayed.
Caused by: cubrid.jdbc.driver.CUBRIDException: Attempt to access a closed Statement.

TRIGGER_ACTION

Turn on or off of the trigger's action about the broker which specified this parameter. Specify ON or OFF as a value; The default is ON.

Logging¶

ACCESS_LOG

ACCESS_LOG is a parameter to configure whether to store the access log of the broker. The default value is OFF. The name of the access log file for the broker is broker_name.access and the file is stored under $CUBRID/log/broker directory.

ACCESS_LOG_DIR

ACCESS_LOG_DIR specifies the directory for broker access logging files(ACCESS_LOB) to be created. The default is log/broker.

ACCESS_LOG_MAX_SIZE

ACCESS_LOG_MAX_SIZE specifies the maximum size of broker access logging files(ACCESS_LOG); if a broker access logging file is bigger than a specified size, this file is backed up into the name of broker_name.access.YYYYMMDDHHMISS, then logging messages are written to the new file(broker_name.access). The default is 10M and the maximum is 2G. It can be dynamically changed during operating a broker.

ERROR_LOG_DIR

ERROR_LOG_DIR is a parameter to configure default directory in which error logs about broker is stored. The default value is log/broker/error_log. The log file name for the broker error is broker_ name_id.err.

LOG_DIR

LOG_DIR is a parameter to configure the directory where SQL logs are stored. The default value is log/broker/sql_log. The file name of the SQL logs is broker_name_id.sql.log.

SLOW_LOG

SLOW_LOG is a parameter to configure whether to log. The default value is ON. If the value is ON, long transaction query which exceeds the time specified in LONG_QUERY_TIME or query where an error occurred is stored in the SLOW SQL log file. The name of file created is broker_name_id.slow.log and it is located under SLOW_LOG_DIR.

SLOW_LOG_DIR

SLOW_LOG_DIR is a parameter to configure the location of directory where the log file is generated. The default value is log/broker/sql_log.

SQL_LOG

SQL_LOG is a parameter to configure whether to leave logs for SQL statements processed by CAS when CAS handles requests from a client. The default value is ON. When this parameter is configured to ON, all logs are stored. The log file name becomes broker_name_id.sql.log. The file is created in the log/broker/sql_log directory under the installation directory. The parameter values are as follows:

OFF : Does not leave any logs.

ERROR : Stores logs for queries which occur an error. only queries where an error occurs.

NOTICE : Stores logs for the long-duration execution queries which exceeds the configured time/transaction, or leaves logs for queries which occur an error.

TIMEOUT : Stores logs for the long-duration execution queries which exceeds the configured time/transaction.

ON / ALL : Stores all logs.

SQL_LOG_MAX_SIZE

SQL_LOG_MAX_SIZE is a parameter to configure the maximum size of the SQL log file. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB) or megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, M will be applied. The default value is 10,000* (KB).

If the size of the SQL log file, which is created when the SQL_LOG parameter is configured to ON, reaches to the size configured by the parameter, broker_name_id.sql.log.bak is created.

If the size of the SLOW SQL log file, which is created when the SLOW_LOG parameter is configured to ON, reaches to the size configured by the parameter, broker_name_id.slow.log.bak is created.

SHARD¶

To use SHARD feature, configure the below parameters in cubrid_broker.conf as referring to cubrid_broker.conf.shard.

SHARD

It specifies to activate/deactivate SHARD feature. You can set this value as ON or OFF. The default is OFF.

SHARD_CONNECTION_FILE

The path of the shard connection file. The shard connection file should be located in $CUBRID/conf. For more information, see the shard connection file.

SHARD_DB_NAME

The name of the shard DB, used to verify the request for connection from an application.

SHARD_DB_PASSWORD

The user password of the backend shard DB, used to connect to the backend DBMS for the CAS process as well as to verify the request for connection from an application. Passwords of all shard DBs should be identical.

The environment variable can be used when you don't want to expose SHARD_DB_PASSWORD to cubrid_broker.conf. The format of this environment variable name is <broker_name>_SHARD_DB_PASSWORD, and <broker_name> always should be changed as upper cases. For example, if the name of broker is shard1, the name of an environment variable which configures the shard DB password will be SHARD1_SHARD_DB_PASSWORD. But, if the SHARD feature is restarted by "cubrid broker restart" command, the environment variable of SHARD_DB_PASSWORD or the value of SHARD_DB_PASSWORD in cubrid_broker.conf must be configured.
export SHARD1_SHARD_DB_PASSWORD=shard123
Note

SHARD_DB_USER/SHARD_DB_PASSWORD parameters is deprecated. Therefore, it is recommended to deliver the connection information in an application.

SHARD_DB_USER

The name of the backend shard DB user, used to connect to the backend DBMS for the CAS process as well as to verify the request for connection from an application. User names on all shard DBs should be identical.

Note

SHARD_DB_USER/SHARD_DB_PASSWORD parameters is deprecated. Therefore, it is recommended to deliver the connection information in an application.

SHARD_IGNORE_HINT

When this value is ON, the hint provided to connect to a specific shard is ignored and the database to connect is selected based on the defined rule. The default value is OFF. It can be used to balance the read load while all databases are copied with the same data. For example, to give the load of an application to only one node among several replication nodes, the proxy automatically determines the node (database) with one connection to a specific shard.

SHARD_KEY_FILE

The path of the shard key configuration file. The shard key configuration file should be located in $CUBRID/conf. For more information, see the shard key configuration file.

SHARD_KEY_FUNCTION_NAME

The parameter to specify the name of the user hash function for shard key. For more information, see Setting User-Defined Hash Function.

SHARD_KEY_LIBRARY_NAME

Specify the library path loadable at runtime to specify the user hash function for the shard key. If the SHARD_KEY_LIBRARY_NAME parameter is set, the SHARD_KEY_FUNCTION_NAME parameter should also be set. For more information, see Setting User-Defined Hash Function.

SHARD_KEY_MODULAR

The parameter to specify the range of results of the default shard key hash function. The result of the function is shard_key(integer) % SHARD_KEY_MODULAR. The minimum value is 1, and the maximum value is 256. For related issues, see shard key configuration file and Setting User-Defined Hash Function.

SHARD_MAX_CLIENTS

The number of applications that can be concurrently connected by using the proxy. The default value is 256 and the maximum value is 10,000 per proxy.

SHARD_MAX_PREPARED_STMT_COUNT

The maximum size of statement pool managed by proxy. The default is 10,000.

SHARD_NUM_PROXY

The number of proxy processes.

SHARD_PROXY_CONN_WAIT_TIMEOUT

If there is no request anymore during the time specified in this parameter, CAS disconnect with DB. The default is 8h. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, second(s) will be applied. CAS which has a previous password should be exit because it cannot be used anymore; this feature protects that CAS is still kept unnecessarily.

SHARD_PROXY_LOG

The proxy log level. It can be set to one of the following values:

ALL : All logs

ON : All logs

SHARD : Logs for selecting and processing shard DBs.

SCHEDULE : Logs for scheduling tasks.

NOTICE : Logs for key notices.

TIMEOUT : Logs for timeouts.

ERROR : Logs for errors.

NONE : No logs recorded.

OFF : No logs recorded.

SHARD_PROXY_LOG_DIR

The directory path where the proxy logs will be saved.

SHARD_PROXY_LOG_MAX_SIZE

The maximum size of the proxy log file. You can set a unit as B, K, M or G, which stands for bytes, kilobytes(KB) or megabytes(MB) or gigabytes(GB) respectively. If you omit the unit, K will be applied. The maximum value is 1,000,000(KB).

SHARD_PROXY_SHM_ID

A parameter to configure the ID of shared memory used by proxy

SHARD_PROXY_TIMEOUT

The maximum waiting time by which the statement is prepared or CAS is available to use. The default value is 30(seconds). If this value is 0, the waiting time is decided by the value of the query_timeout system parameter; if the value of query_timeout is also 0, the waiting time is infinite. IF the value SHARD_PROXY_TIMEOUT is larger than 0, the maximum value between query_timeout and SHARD_PROXY_TIMEOUT decides the waiting time. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, s will be applied.

Note

Required parameters for configuring proxy

To configure CUBRID proxy, you should specify SHARD_MAX_CLIENTS, MAX_NUM_APPL_SERVER and SHARD_NUM_PROXY.

In Linux, the number of file descriptors(fd) per proxy process is limited as follows.
- "((SHARD_MAX_CLIENTS + MAX_NUM_APPL_SERVER) / SHARD_NUM_PROXY) + 256" <= 10,000

The following are detail descriptions on above formulas.

SHARD_MAX_CLIENTS is the maximum number of applications which access the SHARD system.
MAX_NUM_APPL_SERVER is the maximum number of all CASes which can access proxy system.
SHARD_NUM_PROXY is the maximum number of proxy processes which can use on the SHARD system.
"SHARD_MAX_CLIENTS / SHARD_NUM_PROXY" is the maximum number of applications which can access per proxy process.
"MAX_NUM_APPL_SERVER / SHARD_NUM_PROXY" is the maximum number of CASes which can access per proxy process.
256 is the number of file descriptors which are used internally per process on Linux.

As an example of configuring SHARD parameters in Linux system, if you specify the maximum concurrent access number of applications (SHARD_MAX_CLIENTS) as 5,000, the maximum number of CASes(MAX_NUM_APPL_SERVER) as 200 and the maximum number of proxy process(SHARD_NUM_PROXY) as 1, then file descriptors per proxy process becomes (5,000 + 200)/1 + 256 = 5,456, and it is less than 10,000; it is possible configuration.

Regarding above, the following is the connection-relationship between each process. "proxy" intermediates a connection between "app. client" and "CAS".

In the below, [] indicates a process, and -> indicates the requesting direction.

[app. client]   --(initial access request)-----------> [broker] (select proxy)
                <--(announce proxy to access)---
                -------------------------------------> [proxy] --(select CAS)--> [CAS] ---> [DB server]
                <-------------------------------------         <----------------       <---
                <--(now use the same proxy)---------->         <--------------->       <-->

broker is used only once when the application client requires the initial access, and then CUBRID keep connections among "[app. client] - [proxy] - [CAS] - [DB Server]".

Also, CAS keeps connection with DB server after DB connection is completed.

Etc¶

MAX_STRING_LENGTH

MAX_STRING_LENGTH is a parameter to configure the maximum string length for BIT, VARBIT, CHAR and VARCHAR data types. If the value is -1, which is the default value, the length defined in the database is used. If the value is 100, the value acts like 100 being applied even when a certain attribute is defined as VARCHAR(1000).

SERVICE

SERVICE is a parameter to configure whether to run the broker. It can be either ON or OFF. The default value is ON. The broker can run only when this value is configured to ON.

SOURCE_ENV

SOURCE_ENV is a parameter used to determine the file where the operating system variable for each broker is configured. The extension of the file must be env. All parameters specified in cubrid.conf can also be configured by environment variables. For example, the lock_timeout parameter in cubrid.conf can also be configured by the CUBRID_LOCK_TIMEOUT environment variable. As another example, to block execution of DDL statements on broker1, you can configure CUBRID_BLOCK_DDL_STATEMENT to 1 in the file specified by SOURCE_ENV.

An environment variable, if exists, has priority over cubrid.conf. The default value is cubrid.env.

HA Configuration¶

Regarding HA configuration, see Environment Configuration.

SHARD Configuration¶

Regarding SHARD configuration, see Configuration.